/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package camada.bancodedados.daoimplemente.xml;

import eloma.classe.notasfiscais.Destinatario;
import eloma.classe.notasfiscais.Emitente;
import eloma.classe.notasfiscais.NotaFiscal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author GEMADA
 */
public class XMLDAOImpl {

    int key = 0;

    public void inserirNotaFiscal(NotaFiscal n, Connection con) throws SQLException {
        PreparedStatement pstm = null;
        try {
            final String SQL_INSERIR_NOTA
                    = "INSERT INTO nota_fiscal(cUf,cNf,natOp,indPag,mod)values(?,?,?,?,?)";
            pstm = con.prepareStatement(SQL_INSERIR_NOTA);
            //(cUf,cNf,natOp,indPag,mod,serie,nNf,dEmi,tpNf,cMunFg,tpImp,tpEmis,cDv,tpAmb,finNfe,procEmi,verProc)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            pstm.setString(1, n.getcUf());
            pstm.setString(2, n.getcNf());
            pstm.setString(3, n.getNatOp());
            pstm.setString(4, n.getIndPag());
            pstm.setString(5, n.getMod());
            //pstm.setString(6, n.getSerie());
//            pstm.setString(7, n.getnNF());
//            pstm.setString(8, n.getdEmi());
//            pstm.setString(9, n.getTpNF());
//            pstm.setString(10, n.getcMunFG());
//            pstm.setString(11, n.getTpImp());
//            pstm.setString(12, n.getTpEmis());
//            pstm.setString(13, n.getcDV());
//            pstm.setString(14, n.getTpAmb());
//            pstm.setString(15, n.getFinNFe());
//            pstm.setString(16, n.getProcEmi());
//            pstm.setString(17, n.getVerProc());

            pstm.executeUpdate();

            String query = "select idNotaFiscal from nota_fiscal";
            ResultSet rs = pstm.executeQuery(query);

            while (rs.next()) {
                key = rs.getInt("idNotaFiscal");
            }

            inserirEmitente(n.getEmitente(), key, con);

            inserirDestinatario(n.getDestinatario(), key, con);

        } catch (SQLException e) {
            con.rollback();
        }

        con.commit();

    }

    public void inserirEmitente(Emitente e, int idNota, Connection con) throws SQLException {
        PreparedStatement pstm = null;
        try {
            final String SQL_INSERIR_EMITENTE
                    = "INSERT INTO emitente_nota_fiscal(idNota_fiscal,cnpj,xNome,xLg,nro,xBairro,cMun,xMun,uf,cep,xPais,ie,crt)"
                    + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";

            pstm = con.prepareStatement(SQL_INSERIR_EMITENTE);

            pstm.setInt(1, idNota);
            pstm.setString(2, e.getCNPJ());
            pstm.setString(3, e.getxNome());
            pstm.setString(4, e.getxLgr());
            pstm.setString(5, e.getNro());
            pstm.setString(6, e.getxBairro());
            pstm.setString(7, e.getcMun());
            pstm.setString(8, e.getxMun());
            pstm.setString(9, e.getUF());
            pstm.setString(10, e.getCEP());
            pstm.setString(11, e.getxPais());
            pstm.setString(12, e.getIE());
            pstm.setString(13, e.getCRT());

            pstm.executeUpdate();

        } catch (Exception ex) {
            con.rollback();
        }

    }

    private void inserirDestinatario(Destinatario d, int idNota, Connection con) throws SQLException {
        PreparedStatement pstm = null;
        try {
            final String SQL_INSERIR_DESTINATARIO
                    = "insert into destinatario_nota_fiscal(idNota_fiscal,cnpj,xNome,xLg,nro,xBairro,cMun,xMun,uf,cep,xPais,ie)"
                    + " values(?,?,?,?,?,?,?,?,?,?,?,?)";

            pstm = con.prepareStatement(SQL_INSERIR_DESTINATARIO);

            pstm.setInt(1, idNota);
            pstm.setString(2, d.getCNPJ());
            pstm.setString(3, d.getxNome());
            pstm.setString(4, d.getxLgr());
            pstm.setString(5, d.getNro());
            pstm.setString(6, d.getxBairro());
            pstm.setString(7, d.getcMun());
            pstm.setString(8, d.getxMun());
            pstm.setString(9, d.getUF());
            pstm.setString(10, d.getCEP());
            pstm.setString(11, d.getxPais());
            pstm.setString(12, d.getIE());

            pstm.executeUpdate();

        } catch (SQLException e) {
            con.rollback();
        }
    }

}
